/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Models.Payment;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

/**
 *
 * @author Cham to tet
 */
public class PaymentDAO {

    public int addPayment(Payment payment) {
        int result = 0;
        try {
            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement stm = con.prepareStatement("Insert into Payment values(?,?,?,?,?,?)");

            if (payment.getCompanyID() == 0) {
                stm.setNull(1, java.sql.Types.INTEGER);
            } else {
                stm.setInt(1, payment.getCompanyID());
            }

            if (payment.getDriversID() == 0) {
                stm.setNull(2, java.sql.Types.INTEGER);
            } else {
                stm.setInt(2, payment.getDriversID());
            }

            if (payment.getAdvertiseID() == 0) {
                stm.setNull(3, java.sql.Types.INTEGER);
            } else {
                stm.setInt(3, payment.getAdvertiseID());
            }

            stm.setFloat(4, payment.getMoney());
            Calendar cal = Calendar.getInstance();
            if (payment.getExtensionType().equalsIgnoreCase("Monthly")) {
                cal.add(Calendar.MONTH, payment.getAmount());
            } else {
                cal.add(Calendar.MONTH, (payment.getAmount() * 3));
            }
            stm.setDate(5, new java.sql.Date(cal.getTimeInMillis()));
            stm.setBoolean(6, false);
            result = stm.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }

    public int statisticPayment() {
        int total = 0;
        try {
            Connection con = DBConnection.myConnection();
            PreparedStatement st = con.prepareStatement("Select count(*) from Payment");
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return total;
    }

    public int getAllPaid() {
        int total = 0;
        try {
            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement st = con.prepareStatement("select count(*) from Payment where Status = 'true' ");
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return total;
    }

    public int getAllUnPaid() {
        int total = 0;
        try {
            Connection con = DBConnection.myConnection();
            PreparedStatement st = con.prepareStatement("select count(*) from Payment where Status = 'false' ");
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return total;
    }
    static ResultSet rss = null;
    static Connection conn = null;
    static java.sql.Statement stmm = null;

    public static List<Payment> getListPaymentCompany() throws Exception {
        List lst = new ArrayList();
        conn = (Connection) DBConnection.myConnection();
        stmm = conn.createStatement();
        rss = stmm.executeQuery("select c.Name, p.ID, [Money], [ExpirationDate], p.Status"
                + " from Payment p inner join Company c"
                + " on p.CompanyID = c.ID");
        while (rss.next()) {
            Payment dr = new Payment();
            dr.setNameC(rss.getString(1));
            dr.setId(rss.getInt(2));
            dr.setMoney(rss.getFloat(3));
            dr.setExpirationDate(rss.getDate(4));
            dr.setStatus(rss.getBoolean(5));
            lst.add(dr);
        }
        conn.close();
        return lst;
    }

    public static List<Payment> getListPaymentAdvertise() throws Exception {
        List lst = new ArrayList();
        conn = (Connection) DBConnection.myConnection();
        stmm = conn.createStatement();
        rss = stmm.executeQuery("select a.Name, p.ID, [Money], [ExpirationDate], p.Status"
                + " from Payment p inner join Advertise a"
                + " on p.AdvertiseID = a.ID");
        while (rss.next()) {
            Payment dr = new Payment();
            dr.setNameA(rss.getString(1));
            dr.setId(rss.getInt(2));
            dr.setMoney(rss.getFloat(3));
            dr.setExpirationDate(rss.getDate(4));
            dr.setStatus(rss.getBoolean(5));
            lst.add(dr);
        }
        conn.close();
        return lst;
    }

    public static List<Payment> getListPaymentAdvertiseExpires() throws Exception {
        List lst = new ArrayList();
        conn = (Connection) DBConnection.myConnection();
        stmm = conn.createStatement();
        rss = stmm.executeQuery("select a.Name, p.ID, [Money], [ExpirationDate], p.Status"
                + " from Payment p inner join Advertise a"
                + " on p.AdvertiseID = a.ID where ExpirationDate < GETDATE()");
        while (rss.next()) {
            Payment dr = new Payment();
            dr.setNameA(rss.getString(1));
            dr.setId(rss.getInt(2));
            dr.setMoney(rss.getFloat(3));
            dr.setExpirationDate(rss.getDate(4));
            dr.setStatus(rss.getBoolean(5));
            lst.add(dr);
        }
        conn.close();
        return lst;
    }

    public static List<Payment> getListPaymentDriver() throws Exception {
        List lst = new ArrayList();
        conn = (Connection) DBConnection.myConnection();
        stmm = conn.createStatement();
        rss = stmm.executeQuery("select d.Name, p.ID, [Money], [ExpirationDate], p.Status"
                + " from Payment p inner join Driver d"
                + " on p.DriverID = d.ID");
        while (rss.next()) {
            Payment dr = new Payment();
            dr.setNamD(rss.getString(1));
            dr.setId(rss.getInt(2));
            dr.setMoney(rss.getFloat(3));
            dr.setExpirationDate(rss.getDate(4));
            dr.setStatus(rss.getBoolean(5));
            lst.add(dr);
        }
        conn.close();
        return lst;
    }

    public static List<Payment> getListPaymentDriverExpires() throws Exception {
        List lst = new ArrayList();
        conn = (Connection) DBConnection.myConnection();
        stmm = conn.createStatement();
        rss = stmm.executeQuery("select d.Name, p.ID, [Money], [ExpirationDate], p.Status"
                + " from Payment p inner join Driver d"
                + " on p.DriverID = d.ID where ExpirationDate < GETDATE()");
        while (rss.next()) {
            Payment dr = new Payment();
            dr.setNamD(rss.getString(1));
            dr.setId(rss.getInt(2));
            dr.setMoney(rss.getFloat(3));
            dr.setExpirationDate(rss.getDate(4));
            dr.setStatus(rss.getBoolean(5));
            lst.add(dr);
        }
        conn.close();
        return lst;
    }

    public static List<Payment> getListPaymentCompanyExpires() throws Exception {
        List lst = new ArrayList();
        conn = (Connection) DBConnection.myConnection();
        stmm = conn.createStatement();
        rss = stmm.executeQuery("select c.Name, p.ID, [Money], [ExpirationDate], p.Status"
                + " from Payment p inner join Company c"
                + " on p.CompanyID = c.ID where ExpirationDate < GETDATE()");
        while (rss.next()) {
            Payment dr = new Payment();
            dr.setNameC(rss.getString(1));
            dr.setId(rss.getInt(2));
            dr.setMoney(rss.getFloat(3));
            dr.setExpirationDate(rss.getDate(4));
            dr.setStatus(rss.getBoolean(5));
            lst.add(dr);
        }
        conn.close();
        return lst;
    }

    public static void deletePayment(int id) throws Exception {
        conn = (Connection) DBConnection.myConnection();
        PreparedStatement st = conn.prepareStatement("delete from Payment where ID=" + id);
        st.executeUpdate();
    }

    public static void editPayment(Payment pay) throws Exception {
        conn = (Connection) DBConnection.myConnection();
        String sql = "update Payment set [Status]=? where ID=?";
        PreparedStatement st = conn.prepareStatement(sql);
        st.setBoolean(1, pay.isStatus());
        st.setInt(2, pay.getId());
        st.executeUpdate();
    }
}
